package com.aquila.datacontrol.database;

import java.util.ArrayList;

import com.aquila.datacontrol.SystemConfig;
import com.aquila.datacontrol.object.OtherObject;

import android.R.integer;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;
/**
 * 读取数据的基础类
 * @author Administrator
 *
 */
public class OtherDataSqlite extends SQLiteOpenHelper {
	public static final String TABLE_NAME = "Paragraph";
//	public static final String DATABASE_NAME = "holybible";
	public static final int Version =1;
	private SQLiteDatabase db;
	

//	private int ParagraphID ; 
//	private int ParagraphNumber ;
//	private int BookID ;
//	private String BookName ;
//	private int ChapterID ;
//	private int ChapterNumber ;
//	private String Content ;
//	private String EnContent ;
//	private String Remark ;
	
	
	private final String KEY_ParagraphID = "ParagraphID" ;
	private final String KEY_ParagraphNumber = "ParagraphNumber" ;
	private final String KEY_BookID = "BookID" ;	//书卷的索引
	private final String KEY_BookName = "BookName" ; //书卷全名
	private final String KEY_ChapterID = "ChapterID" ; // 章节的索引
	private final String KEY_ChapterNumber = "ChapterNumber" ; // 章节的索引
	private final String KEY_Content = "Content" ; // 章节的索引
	private final String KEY_EnContent = "EnContent" ; // 章节的索引
	private final String KEY_Remark = "Remark" ; // 章节的索引
	/***
	 * 创建数据库表
	 * @param db
	 *2012-7-30 下午1:15:53
	 */
	private void createTable(SQLiteDatabase db){
		String sqlstr = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME + " ( "+
	            KEY_ParagraphID + " INTEGER, " +
	            KEY_ParagraphNumber + " INTEGER, " +
	            KEY_BookName + " VARCHAR(50), " +
	            KEY_ChapterID + " INTEGER, " +
	            KEY_ChapterNumber+ " INTEGER, " +
	            KEY_Content+ " VARCHAR(1024), " +
	            KEY_EnContent+ " VARCHAR(1024), " +
	            KEY_Remark+ " VARCHAR, " +
	            KEY_BookID + " INTEGER);"
	            ;
		try{
			db.execSQL(sqlstr);
		}catch (SQLException e) {
			e.printStackTrace() ;
		}
	}
	private Context context ;
	public OtherDataSqlite(Context context) {
		super(context, SystemConfig.DATABASE_NAME, null, Version);
		this.context = context ;
		db = getWritableDatabase() ;
		createTable(db) ;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
		onCreate(db);
	}
	
	/***
	 * 打开数据库
	 *2012-7-30 下午1:37:13
	 */
	public void open() {
		if (db == null || !db.isOpen()) {
			db = getWritableDatabase() ;
		}
	}
	
	/**
	 * 关闭数据库
	 */
	public void close(){
		if (db.isOpen()) {
			db.close() ;
		}
	}
	
	
	
	/**
	 * 插入一条数据
	 * @param object
	 *2012-8-8 上午10:09:36
	 */
	public boolean insertData(OtherObject object){
		if (object == null) {
			return false;
		}
			ContentValues contentValues = inputContentValues(object) ;
			db.insert(TABLE_NAME, null, contentValues) ;
			return true;
	}
	
	
	
	
	
	
	public void deleteAllData(){
		db.delete(TABLE_NAME, null, null) ;
	}
	
	public String  getEnContent(int bookIndex, int chapterIndex, int sectionIndex) {
		String sqlString = "select * from "+TABLE_NAME+" where "+
				KEY_BookID +" = '" + bookIndex  +"' and " +
				KEY_ChapterNumber + " = '" + chapterIndex + "' and " +
				KEY_ParagraphNumber + " = '" + sectionIndex +"'";
		Cursor cursor = db.rawQuery(sqlString, null) ;
		OtherObject otherObject = null ;
		String enSection ="";
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst() ;
			enSection = cursor.getString(cursor.getColumnIndex(KEY_EnContent)) ;
//			otherObject = readDataResultObject(cursor) ;
			cursor.close() ;
		}
		return enSection ;
	}
	
	public String  getCNContent(int bookIndex, int chapterIndex, int sectionIndex) {
		String sqlString = "select * from "+TABLE_NAME+" where "+
				KEY_BookID +" = '" + bookIndex  +"' and " +
				KEY_ChapterNumber + " = '" + chapterIndex + "' and " +
				KEY_ParagraphNumber + " = '" + sectionIndex +"'";
		Cursor cursor = db.rawQuery(sqlString, null) ;
//		OtherObject otherObject = null ;
		String cnSection ="";
		if (cursor != null && cursor.getCount() > 0) {
			cursor.moveToFirst() ;
			cnSection = cursor.getString(cursor.getColumnIndex(KEY_Content)) ;
//			otherObject = readDataResultObject(cursor) ;
			cursor.close() ;
		}
		return cnSection ;
	}
	
	public OtherObject getOtherObject(int bookIndex, int chapterIndex, int sectionIndex) {
		String sqlString = "select * from "+TABLE_NAME+" where "+
				KEY_BookID +" = '" + bookIndex  +"' and " +
				KEY_ChapterNumber + " = '" + chapterIndex + "' and " +
				KEY_ParagraphNumber + " = '" + sectionIndex +"'";
		Cursor cursor = db.rawQuery(sqlString, null) ;
		OtherObject otherObject = null ;
		if (cursor != null && cursor.getCount() > 0) {
//			String enSection = cursor.getString(cursor.getColumnIndex(KEY_EnContent)) ;
		cursor.moveToFirst() ;
		otherObject = readDataResultObject(cursor) ;
		cursor.close() ;
		}
		return otherObject ;
	}
	/**
	 * 获取保存历史的记录
	 * @return
	 *2012-7-30 下午1:57:52
	 */
	public int getDataCount(){
		String sqlString = "select * from " + TABLE_NAME ;
		Cursor cursor = db.rawQuery(sqlString, null) ;
		return cursor.getCount() ;
	}
	
	
	
	/**
	 * 封装读取数据的方法
	 * @param cursor
	 * @return
	 *2012-8-8 上午10:00:08
	 */
	private OtherObject readDataResultObject(Cursor cursor){
		OtherObject object= new OtherObject() ;
		object.setBookID(cursor.getInt(cursor.getColumnIndex(KEY_BookID))) ;
		object.setBookName(cursor.getString(cursor.getColumnIndex(KEY_BookName))) ;
		object.setChapterID(cursor.getInt(cursor.getColumnIndex(KEY_ChapterID))) ;
		object.setChapterNumber(cursor.getInt(cursor.getColumnIndex(KEY_ChapterNumber))) ;
		object.setContent(cursor.getString(cursor.getColumnIndex(KEY_Content))) ;
		object.setEnContent(cursor.getString(cursor.getColumnIndex(KEY_EnContent))) ;
		object.setParagraphID(cursor.getInt(cursor.getColumnIndex(KEY_ParagraphID))) ;
		object.setParagraphNumber(cursor.getInt(cursor.getColumnIndex(KEY_ParagraphNumber))) ;
		object.setRemark(cursor.getString(cursor.getColumnIndex(KEY_Remark))) ;
		
		return object ;
	}
	
	
	/**
	 * 存数据封装的方法
	 * @param object
	 * @return
	 *2012-8-8 上午9:45:14
	 */
	private ContentValues inputContentValues(OtherObject object){
		ContentValues cv = new ContentValues() ;
		cv.put(KEY_BookID, object.getBookID()) ;
		cv.put(KEY_BookName, object.getBookName()) ;
		cv.put(KEY_ChapterID, object.getChapterID()) ;
		cv.put(KEY_ChapterNumber, object.getChapterNumber()) ;
		cv.put(KEY_Content, object.getContent()) ;
		cv.put(KEY_EnContent, object.getEnContent()) ;
		cv.put(KEY_ParagraphID, object.getParagraphID()) ;
		cv.put(KEY_ParagraphNumber, object.getParagraphNumber()) ;
		cv.put(KEY_Remark, object.getRemark()) ;
		return cv ;
	}
	
	
}
